package com.ms.qw.domain.bean;

import com.ms.qw.util.FileTempRef;
import com.ms.qw.util.NumberUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.beans.BeanWrapperImpl;

import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * User: jmacavilca
 * Date: 07/11/11
 */
public class ExcelExporter {
    private static final Logger log = Logger.getLogger(ExcelExporter.class);
    String filePrefix;
    List<CeldaInfo> cols;
    boolean isAutosizeCols = false;

    public ExcelExporter(String filePrefix, List<CeldaInfo> cols) {
        this.filePrefix = filePrefix;
        this.cols = cols;
    }

    public ExcelExporter setAutosizeCols() {
        isAutosizeCols = true;
        return this;
    }

    public File crearExcelData(List<? extends Object> sheetData, String sheetName) {
        Map<String, List<? extends Object>> excelData = new HashMap<String, List<? extends Object>>();
        excelData.put(sheetName, sheetData);
        return crearExcelData(excelData);
    }

    public File crearExcelData(Map<String, List<? extends Object>> excelData) {
        if (cols == null) throw new IllegalArgumentException("Cols es nulo");

        HSSFWorkbook wb = new HSSFWorkbook();

        // Iterar en cada hoja
        for (Iterator<Map.Entry<String, List<? extends Object>>> iterator = excelData.entrySet().iterator(); iterator.hasNext(); ) {
            Map.Entry<String, List<? extends Object>> beanGroup = iterator.next();
            String sheetName = beanGroup.getKey();
            List<? extends Object> beanList = beanGroup.getValue();

            HSSFSheet hoja = wb.createSheet(sheetName);

            if (beanList.size() <= 0)
                continue;
            /////////////////////////Cabecera
            int fila = 0;
            crearFilaTitulos(wb, hoja, fila, cols);

            fila = fila + 1;
            //////////////////////////Data
            crearFilasDetalle(wb, hoja, fila, (List<List>) beanList, cols);

            for (CeldaInfo celdaInfo : cols) {
                hoja.autoSizeColumn(celdaInfo.getColumnaIndex());
            }
        }

        try {
            File file = FileTempRef.buildTmpFile(filePrefix + "-Data", "xls");
            String fullFilePath = file.getAbsolutePath();
            File ff = new File(fullFilePath);
            FileOutputStream foss = new FileOutputStream(ff);
            wb.write(foss);
            foss.close();
            return file;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

    }


    public static <T> void crearFilasDetalle(HSSFWorkbook wb, HSSFSheet hoja, int fila, List<List> beanList,
                                             List<CeldaInfo> cols) {
        long start = System.currentTimeMillis();

        HSSFDataFormat df = wb.createDataFormat();
        HSSFCellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(df.getFormat("yyyy/mm/dd hh:mm"));
        fila = crearFilasDetalle_rows(hoja, fila, beanList, cols, dateStyle);
        long end = System.currentTimeMillis();
        log.debug("FillExcel elapsed " + (end - start) / 1000 + " segs. Filas:" + beanList.size());
    }

    private static <T> int crearFilasDetalle_rows(HSSFSheet hoja, int fila, List<List> beanList, List<CeldaInfo> cols, HSSFCellStyle dateStyle) {
        for (int y = 0; y < beanList.size(); y++) {
            List t = beanList.get(y);
            if (cols.size() != t.size()) {
                System.out.println("error size " + t.size());
                System.out.println("error " + t);
            }
            HSSFRow filaValue = hoja.createRow(fila);
            for (int i = 0; i < cols.size(); i++) {
                HSSFCell celda = filaValue.createCell(i);
                if (i < t.size()) {
                    if (cols.get(i).getTipo().equals("N")) {
                        DecimalFormat df= NumberUtil.buildDecimalFormat();
                        try {
                            if(StringUtils.isNotBlank((String) t.get(i))){
                                celda.setCellValue(df.parse((String) t.get(i)).doubleValue());
                            }else{
                                celda.setCellValue("");
                            }
                        } catch (ParseException e) {
                            e.printStackTrace();
                        }
                    } else if (cols.get(i).getTipo().equals("D")) {
                        SimpleDateFormat sdf= new SimpleDateFormat("dd/MM/yyyy");
                        try {
                            if(StringUtils.isNotBlank((String) t.get(i))){
                                celda.setCellValue(sdf.parse((String) t.get(i)));
                            }else{
                                celda.setCellValue("");
                            }
                        } catch (ParseException e) {
                            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
                        }
                    } else {
                        celda.setCellValue((String) t.get(i));

                    }

                }
            }
            fila = fila + 1;
        }
        return fila;
    }

    public static void crearFilaTitulos(HSSFWorkbook wb, HSSFSheet hoja, int fila, List<CeldaInfo> cols) {
        HSSFFont negritaFont = wb.createFont();
        negritaFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle negritaCellStyle = wb.createCellStyle();
        negritaCellStyle.setFont(negritaFont);

        HSSFRow filaTitulosXSSRow = hoja.createRow(fila);

        for (int i = 0; i < cols.size(); i++) {
            HSSFCell celda = filaTitulosXSSRow.createCell(i);
            celda.setCellStyle(negritaCellStyle);
            celda.setCellValue(cols.get(i).getNombre());
        }

    }

    public static void autoZizeCols(XSSFSheet sheet, int cols) {
        for (int i = 0; i < cols; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    public static class Cols {


        public enum GroupOutline {
            Sum, Distinct
        }

        BeanWrapperImpl beanItemListaCabecera;

        List<String> colHeaders = new ArrayList<String>();
        List<IColWritter> colWritters = new ArrayList<IColWritter>();
        List<GroupOutline> colOutlines = new ArrayList<GroupOutline>();
        // not supported yet
        List<String> colDataFormats = new ArrayList<String>();

        Map<String, XSSFCellStyle> colDataFormatsCachedStyle = new LinkedHashMap<String, XSSFCellStyle>();

        public void init(BeanWrapperImpl beanItemListaCabecera) {
            this.beanItemListaCabecera = beanItemListaCabecera;
        }


        /**
         * Se llama a este metodo antes de escribir cada fila
         */
        public void rowStart(Object bean, int listIdx, int xlsRowNum) {

        }

        /**
         * Se llama a este metodo despues de escribir cada fila
         */
        public void rowEnd(Object bean, int listIdx, int xlsRowNum) {

        }

        public Cols add(String colHeader, String colProperty) {
            return addInternal(colHeader, new ColWriterBean(colProperty), null, null);
        }

        public Cols add(String colHeader, String colProperty, GroupOutline groupOutline, String colDataFormat) {
            return addInternal(colHeader, new ColWriterBean(colProperty), groupOutline, colDataFormat);
        }

        public Cols addFormula(String colHeader, String formula, Class type) {
            return addInternal(colHeader, new ColWritterFormula(formula, type), null, null);
        }

        public Cols addInternal(String colHeader, IColWritter colWriterBean, GroupOutline groupOutline, String colDataFormat) {
            colHeaders.add(colHeader);
            colWritters.add(colWriterBean);
            colOutlines.add(groupOutline);
            colDataFormats.add(colDataFormat);
            return this;
        }

        public int size() {
            return colHeaders.size();
        }

        public String getColHeader(int idx) {
            return colHeaders.get(idx);
        }

        public void writeValue(XSSFCell celda, int idx, BeanWrapperImpl beanRow, XSSFCellStyle dateStyle) {
            IColWritter colWriter = colWritters.get(idx);
            colWriter.writeValue(celda, beanRow, dateStyle);
        }

        public Object getValue(int idx, BeanWrapperImpl beanRow) {
            IColWritter colWriter = colWritters.get(idx);
            return colWriter.getValue(beanRow);
        }


    }

    public static class ColsPrefOrder extends Cols {
        String preferredOrder;
        /**
         * Agrega las otras propiedades al final
         */
        boolean addOtherPropertiesAtEnd = true;

        public ColsPrefOrder(String preferredOrder) {
            this.preferredOrder = preferredOrder;
        }

        public ColsPrefOrder setAddOtherPropertiesAtEnd(boolean addOtherPropertiesAtEnd) {
            this.addOtherPropertiesAtEnd = addOtherPropertiesAtEnd;
            return this;
        }

        public void init(BeanWrapperImpl beanItemListaCabecera) {
            super.init(beanItemListaCabecera);

            colHeaders.clear();
            colWritters.clear();

            List<String> propertyNamesSorted = new ArrayList<String>(
                    preferredOrder == null ? Collections.EMPTY_LIST : Arrays.asList(preferredOrder.split(",")));
            for (int i = propertyNamesSorted.size() - 1; i >= 0; i--) {
                String propertyName = propertyNamesSorted.get(i);
                if (!beanItemListaCabecera.isReadableProperty(propertyName)) {
                    log.warn("Propiedad/getter no existe " + beanItemListaCabecera.getWrappedClass().getSimpleName() + "." + propertyName);
                    propertyNamesSorted.remove(i);
                }
            }
            if (addOtherPropertiesAtEnd) {
                for (PropertyDescriptor propertyDescriptor : beanItemListaCabecera.getPropertyDescriptors()) {
                    if (propertyNamesSorted.contains(propertyDescriptor.getName())) continue;
                    if (propertyDescriptor.getPropertyType().getName().equals("java.lang.Class")) continue;
                    propertyNamesSorted.add(propertyDescriptor.getName());
                }
            }

            for (String propertyName : propertyNamesSorted) {
                add(propertyName, propertyName);
            }

        }


    }

    public static class ColWriterBean implements IColWritter {
        protected String propertyName;

        public ColWriterBean(String propertyName) {
            this.propertyName = propertyName;
        }


        public Object getValue(BeanWrapperImpl beanRow) {
            return getValueNested(beanRow, propertyName);
        }

        private static Object getValueNested(BeanWrapperImpl beanRow, String nestedProperyName) {
            if (nestedProperyName.contains(".")) {
                String[] propertyNames = nestedProperyName.split("\\.");
                Object nestedValue = null;
                for (int i = 0; i < propertyNames.length; i++) {
                    String propertyNameSimple = propertyNames[i];
                    nestedValue = getValueSimple(beanRow, propertyNameSimple);
                    if (i + 1 < propertyNames.length) {
                        if (nestedValue == null) break; // inner object is null
                        beanRow = new BeanWrapperImpl(nestedValue);
                    }
                }
                return nestedValue;
            } else if (beanRow.getWrappedInstance() instanceof Map) {
                return ((Map) beanRow.getWrappedInstance()).get(nestedProperyName);
            } else {
                return getValueSimple(beanRow, nestedProperyName);
            }
        }

        private static Object getValueSimple(BeanWrapperImpl beanRow, String propertyNameSimple) {
            PropertyDescriptor propertyDescriptor = beanRow.getPropertyDescriptor(propertyNameSimple);
            Object valor;
            try {
                valor = beanRow.getPropertyValue(propertyDescriptor.getName());
            } catch (IllegalStateException e) {
                valor = null;
            }
            return valor;
        }


        public void writeValue(XSSFCell celda, BeanWrapperImpl beanRow, XSSFCellStyle dateStyle) {
            Object valor = getValue(beanRow);
            writeValueDirect(celda, valor, dateStyle, null);
        }

        public static void writeValueDirect(XSSFCell celda, Object valor, XSSFCellStyle dateStyle, XSSFCellStyle normalStyle) {
            if (valor == null) {
                celda.setCellValue("");
                if (normalStyle != null) celda.setCellStyle(normalStyle);
            } else if (valor instanceof Number) {
                celda.setCellValue(((Number) valor).doubleValue());
                if (normalStyle != null) celda.setCellStyle(normalStyle);
            } else if (valor instanceof Date) {
                celda.setCellValue(((Date) valor));
                celda.setCellStyle(dateStyle);
            } else {
                celda.setCellValue(String.valueOf(valor));
                if (normalStyle != null) celda.setCellStyle(normalStyle);
            }
        }
    }

    public static class ColWritterFormula implements IColWritter {
        String formula;
        Class type;

        private ColWritterFormula(String formula, Class type) {
            this.formula = formula;
            this.type = type;
        }


        public Object getValue(BeanWrapperImpl beanRow) {
            return formula;
        }

        public void writeValue(XSSFCell celda, BeanWrapperImpl beanRow, XSSFCellStyle dateStyle) {
/*
            HashMap<String, String> map = new HashMap<String, String>();
            map.put("{Row}", String.valueOf(celda.getRowIndex() + 1));
            String formulaEx = StringUtil.replaceFull(formula, map);
            celda.setCellFormula(formulaEx);
            if (type != null && Date.class.isAssignableFrom(type))
                celda.setCellStyle(dateStyle);
*/

        }
    }

    /**
     * User: jcmacavilca
     * Date: 04/10/2011
     */
    public static interface IColWritter {
        Object getValue(BeanWrapperImpl beanRow);

        void writeValue(XSSFCell celda, BeanWrapperImpl beanRow, XSSFCellStyle dateStyle);

    }

    public static void main(String[] args) throws ParseException {


        DecimalFormatSymbols symbols = DecimalFormatSymbols.getInstance(new Locale("es"));
        symbols.setDecimalSeparator('.');
        symbols.setGroupingSeparator(',');
        DecimalFormat df= new DecimalFormat("#,###,###,##0.00", symbols);

        System.out.println(df.parse("-271.66"));
    }
}
